﻿--[数据源]

--禁用无法匹配的药品出库信息
UPDATE dbo.OutStockDrug
SET Enable=0
WHERE DrugId='00000000-0000-0000-0000-000000000000'

--禁用无法匹配的开药记录
UPDATE dbo.ActionPrescriptionRecord
SET Enabled=0
WHERE MaterialId='00000000-0000-0000-0000-000000000000'

--修复打针预约的服务类型
DECLARE @ServiceTypeId NVARCHAR(200),@SubServiceTypeId NVARCHAR(200)
SET @ServiceTypeId=(
	  SELECT TOP 1 did.ItemDetailId AS Id
	  FROM dbo.Base_DataItem bdi
	  JOIN dbo.Base_DataItemDetail did ON did.ItemId=bdi.ItemId
	  WHERE bdi.ItemCode='ServiceType' AND did.EnabledMark=1 AND did.ItemValue='Nurse'
)

SET @SubServiceTypeId=(
	  SELECT TOP 1 d2.ItemDetailId
	  FROM dbo.Base_DataItem bdi
	  JOIN dbo.Base_DataItemDetail did ON did.ItemId=bdi.ItemId
	  JOIN Base_DataItemDetail d2 ON d2.ParentId=did.ItemDetailId
	  WHERE bdi.ItemCode='ServiceType' 
	  AND did.EnabledMark=1 AND did.ItemValue='Nurse'
	  AND d2.LaboratoryOrVaccine=2
)

UPDATE dbo.AppointmentMain
SET ServiceId=@ServiceTypeId
,SubServiceId=@SubServiceTypeId
WHERE EXISTS(
	SELECT 1 FROM [数据源].dbo.PATIENTIMMUNIZATIONTABLE t
	WHERE t.P_CODE =Z__P_CODE COLLATE Chinese_PRC_CI_AS 
	AND t.DATE=Z__DATE COLLATE Chinese_PRC_CI_AS 
	AND t.CLINICCODE=Z__CLINICCODE COLLATE Chinese_PRC_CI_AS
)
GO


UPDATE dbo.AppointmentMain
SET Date=LEFT(Date,4)+'-'+RIGHT( LEFT(Date,6),2)+'-'+RIGHT(Date,2)
WHERE LEN(Date)=8

UPDATE dbo.Material
SET TypeName=(
	SELECT TOP 1 itemname FROM dbo.Base_DataItemDetail
	WHERE ItemDetailId='bbf7ff1b-cee4-436f-8254-4a96efed6f23'
)
WHERE TypeName LIKE '%?%'


--补充用户角色信息
INSERT INTO dbo.Base_UserRelation
        (UserRelationId,UserId,Category,ObjectId,IsDefault,SortCode,CreateDate         )
SELECT NEWID(),bu.UserId,2,bu.RoleId,1,0,GETDATE() 
FROM dbo.Base_User bu
WHERE LEN(Z__DOCTORCODE)>0
AND NOT EXISTS(
	SELECT 1 FROM dbo.Base_UserRelation b1
	WHERE b1.UserId=bu.UserId
)

Go


--问诊开药记录
IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('ActionPrescriptionRecord') AND name='_PK')
ALTER TABLE ActionPrescriptionRecord
ADD [_PK] NVARCHAR(500) NULL
GO


IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('InterrogationReports') AND name='_PK')
ALTER TABLE InterrogationReports
ADD [_PK] NVARCHAR(500) NULL
GO



--病人拓展资料
IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('PatientMain') AND name='Z__FHISTORY')
ALTER TABLE dbo.PatientMain
ADD Z__FHISTORY NVARCHAR(MAX) NULL
Go

UPDATE dbo.PatientMain
SET 
DetailedAddress=		  ISNULL( (
		SELECT TOP 1  Data FROM [数据源].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code AND pe.FIELDTYPE='HADDRESS'
		),PatientMain.DetailedAddress)
,
BusDetailedAddress=  ISNULL( (
		SELECT TOP 1  Data FROM [数据源].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='BADDRESS'
		),PatientMain.DetailedAddress)
,REMARKS=ISNULL((
		SELECT TOP 1  Data FROM [数据源].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='REMARKS'
		),PatientMain.DetailedAddress)
,MOME=ISNULL((
		SELECT TOP 1  Data FROM [数据源].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='PLAN'
		),PatientMain.DetailedAddress)
,Z__FHISTORY=ISNULL((
		SELECT TOP 1  Data FROM [数据源].dbo.PATIENTEXTENDTABLE pe
		WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code 
		AND pe.FIELDTYPE='FHISTORY'
		),PatientMain.DetailedAddress)
WHERE EXISTS(
	SELECT 1 FROM [数据源].dbo.PATIENTEXTENDTABLE pe
	WHERE pe.P_CODE Collate DATABASE_DEFAULT=dbo.PatientMain.Code
)

Go


IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE object_id=OBJECT_ID('Base_Organize') AND name='_PK')
ALTER TABLE Base_Organize
ADD [_PK] NVARCHAR(500) NULL
Go

--默认给System所有新增诊所权限
INSERT INTO dbo.Base_AuthorizeData
        (AuthorizeDataId,AuthorizeType,Category,ObjectId,
         ResourceId,IsRead,SortCode,CreateDate)
SELECT NEWID(),-5,5,'system',OrganizeId,0,99,GETDATE()
FROM dbo.Base_Organize bo
WHERE LEN(_PK)>0 AND NOT EXISTS(
	SELECT 1 FROM dbo.Base_AuthorizeData ba
	WHERE ba.ResourceId=bo.OrganizeId AND AuthorizeType=-5 AND ba.Category=5
)

Go

--诊所默认星期一到星期五工作
INSERT INTO dbo.ClinicWorkingSetting
        (Id,ClinicId,StartTime,EndTime,Interval,CreateTime,
         WorkingWeek,NotCareNoShowTime,AppointmentStart,AppointmentEnd,
         CancelNumber)
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),1,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),2,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),3,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),4,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
UNION ALL
SELECT NEWID(),bo.OrganizeId,dbo.F_GetTimeFromInt(bo.Z__START_AT),dbo.F_GetTimeFromInt(bo.Z__END_AT)
,bo.Z__INTERVAL,GETDATE(),5,4,4,14,1
FROM dbo.Base_Organize bo
WHERE LEN(bo.[_PK])>0
AND Z__START_AT IS NOT NULL
AND NOT EXISTS(
	SELECT 1 FROM dbo.ClinicWorkingSetting cws
	WHERE cws.ClinicId=bo.OrganizeId
)
Go



--根据预约信息补全队列信息
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('QueueAttendee') AND name='_PK')
	ALTER TABLE QueueAttendee 
	ADD _PK NVARCHAR(400) NULL
GO

IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('AppointmentMain') AND name='Z__CLINICCODE')
	ALTER TABLE AppointmentMain 
	ADD Z__CLINICCODE NVARCHAR(400) NULL
GO
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('AppointmentMain') AND name='Z__DOCTORCODE')
	ALTER TABLE AppointmentMain 
	ADD Z__DOCTORCODE NVARCHAR(400) NULL
GO
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('AppointmentMain') AND name='Z__DATE')
	ALTER TABLE AppointmentMain 
	ADD Z__DATE NVARCHAR(400) NULL
GO
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('AppointmentMain') AND name='Z__P_CODE')
	ALTER TABLE AppointmentMain 
	ADD Z__P_CODE NVARCHAR(400) NULL
GO


INSERT INTO dbo.QueueAttendee
        (Id,ClinicId,QueueManageId,PatientMainId,Type,Date,QueueTime,
         QueueOrder,Status,CallTime,CreateTime,
         AppointmentId,Express,CaseId,
         Source,Enabled,_PK)
SELECT NEWID(),ClinicId,'',PatientMainId,1,Date,CreateTime,
99,2,CreateTime,CreateTime,Id,0,Z__P_CODE+'_'+Z__CLINICCODE+'_'+Z__DATE+'_'+Z__DOCTORCODE
,3,1,_PK
FROM dbo.AppointmentMain am
WHERE LEN(_PK)>0
AND NOT EXISTS(
	SELECT 1 FROM dbo.QueueAttendee q
	WHERE q._PK=am._PK
)
GO


DECLARE _CUR CURSOR READ_ONLY STATIC FOR
SELECT t.name,c.name
FROM sys.columns c 
JOIN sys.tables t ON t.object_id = c.object_id
JOIN sys.objects o ON o.object_id=t.object_id
WHERE o.schema_id=1
AND c.system_type_id=35

DECLARE @Table NVARCHAR(200),@Column NVARCHAR(200),@Sql NVARCHAR(MAX)
OPEN _CUR
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @Table,@Column;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @Sql='ALTER TABLE dbo.['+@Table+']'
	+CHAR(13)+CHAR(10)+'ALTER COLUMN '+@Column+' NVARCHAR(MAX) NULL'
	+CHAR(13)+CHAR(10)
	EXECUTE sp_executesql @Sql
END
CLOSE _CUR
DEALLOCATE _CUR
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID('QueueAttendee')=object_id AND name='_PK')
	ALTER TABLE QueueAttendee 
	ADD _Pk NVARCHAR(500) NULL
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID('ActionPrescriptionRecord')=object_id AND name='_Pk')
    ALTER TABLE ActionPrescriptionRecord 
	ADD _Pk NVARCHAR(500) NULL
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID('ActionPrescriptionRecord')=object_id AND name='Z__TIMESTAMP')
	ALTER TABLE ActionPrescriptionRecord 
	ADD Z__TIMESTAMP NVARCHAR(500) NULL	
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID('ActionPrescriptionRecord')=object_id AND name='Z__CLINICCODE')
    ALTER TABLE ActionPrescriptionRecord 
	ADD Z__CLINICCODE NVARCHAR(500) NULL
GO
IF NOT EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID('ActionPrescriptionRecord')=object_id AND name='Z__P_CODE')  
	ALTER TABLE ActionPrescriptionRecord 
	ADD Z__P_CODE NVARCHAR(500) NULL
GO

--根据问诊开药记录补全问诊记录
DECLARE _CUR CURSOR READ_ONLY STATIC FOR
SELECT Id FROM ActionPrescriptionRecord apr
WHERE NOT EXISTS(
	SELECT 1 FROM dbo.InterrogationReports ir
	WHERE ir.CaseId=apr.CaseID
	AND ir.Enabled=1
)
DECLARE @Id NVARCHAR(200),@CaseId NVARCHAR(200)
OPEN _CUR
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @Id;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @CaseId =(
		SELECT Z__P_Code+'_'+Z__CLINICCODE+'_'+dbo.F_GetFormatDate(Z__TIMESTAMP,'yyyyMMddHHmm')+'_'+Z__DOCTORCODE
		FROM ActionPrescriptionRecord
		WHERE Id=@Id
	);

	UPDATE ActionPrescriptionRecord
	SET CaseID=@CaseId
	WHERE Id=@Id;
	IF Exists(SELECT * FROM InterrogationReports ir Where ir.CaseId=@CaseId)
		Continue;
	INSERT INTO dbo.InterrogationReports
        (Id,CaseId,PatientMainId,
         Enabled,CreateDate,_PK,CreateUserId,CreateUserName,Z__TIMESTAMP,Z__CLINICCODE)
	SELECT NEWID(),apr.CaseID,apr.PatientMainId,1,apr.Z__TIMESTAMP
	,Z__P_CODE+CONVERT(NVARCHAR(200),apr.Z__TIMESTAMP,21)+Z__CLINICCODE AS _PK
	,'XDQ','补充数据309'
	,Z__TIMESTAMP,Z__CLINICCODE
	FROM dbo.ActionPrescriptionRecord apr
	WHERE apr.Id=@Id
END
CLOSE _CUR
DEALLOCATE _CUR
GO


--根据问诊记录补全队列信息
INSERT INTO dbo.QueueAttendee
        (Id,ClinicId,QueueManageId,PatientMainId,Type,Date,QueueTime,
         QueueOrder,Status,CreateTime,
         AppointmentId,Express,CaseId,
         Source,Enabled,_PK)
SELECT NEWID(),(
	SELECT top 1 OrganizeId
	FROM base_organize b
	where b.EnCode=ir.Z__CLINICCODE
),ISNULL((
	SELECT TOP 1 UserId FROM dbo.DoctorMain 
	WHERE Z__DOCTORCODE=ir.Z__DOCTORCODE
),'NUL'),ir.PatientMainId,0,dbo.F_GetFormatDate(ir.Z__TIMESTAMP,'yyyy-MM-dd'),dbo.F_GetFormatDate(ir.Z__TIMESTAMP,'HH:mm')
,999,3,ir.Z__TIMESTAMP,(
	SELECT TOP 1 Id FROM dbo.AppointmentMain am
	WHERE am.Z__DATE=ir.Z__DATE
	AND am.Z__P_CODE=ir.Z__P_CODE
	--AND am.Z__CLINICCODE=ir.Z__CLINICCODE
	--AND am.Z__DOCTORCODE=ir.Z__DOCTORCODE
),0,ir.CaseId,2,1,ir.CaseId
FROM dbo.InterrogationReports ir
WHERE NOT EXISTS(
	SELECT 1 FROM dbo.QueueAttendee qa
	WHERE qa.CaseId=ir.CaseId
)
AND LEN(ir.Z__TIMESTAMP)>0

GO

--根据账单信息补全队列信息
IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('BillInfo') AND name='Z__CLINICCODE')
INSERT INTO dbo.QueueAttendee
        (Id,ClinicId,QueueManageId,PatientMainId,Type,Date,QueueTime,
         QueueOrder,Status,CreateTime,
         AppointmentId,Express,CaseId,
         Remark,Source,Enabled,
         _Pk)
--存在多个账单共用一个CASEID的情况
--CaseId:008258CTL20130430Appointment
SELECT NEWID(),ISNULL((
	SELECT TOP 1 bo.OrganizeId FROM dbo.Base_Organize bo
	WHERE bo.EnCode=b.Z__CLINICCODE
),'NUL'),ISNULL((
	SELECT TOP 1 id FROM dbo.Base_User bu
	WHERE bu.EnCode=b.Z__DOCTORCODE
),'NUL'),b.PatientId,0,b.Z__DATE,b.BillTime,
99,5,b.BillTime,(
	SELECT TOP 1 Id FROM dbo.AppointmentMain am
	WHERE am.Z__P_CODE+'_'+am.Z__CLINICCODE+'_'+am.Z__DATE+'_'+am.Z__DOCTORCODE=b.CaseId
),0,b.CaseId,b.Remark,2,1,b.CaseId
FROM dbo.BillInfo b
WHERE NOT EXISTS(
	SELECT 1 FROM dbo.QueueAttendee qa
	WHERE qa.CaseId=b.CaseId
)
AND LEN(b.Z__TIMESTAMP)>0

GO

--根据队列信息补全预约信息
DECLARE _CUR CURSOR STATIC READ_ONLY FOR
SELECT Id FROM QueueAttendee 
WHERE AppointmentId IS NULL OR AppointmentId='NUL'
DECLARE @QId NVARCHAR(200) ,@AId NVARCHAR(200),@DId NVARCHAR(200),@DCode NVARCHAR(200)
OPEN _CUR
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @QId
	IF(@@FETCH_STATUS!=0)
	BREAK;
	SET @AId=NEWID()
	SET @DCode=(SELECT TOP 1 irp.Z__DOCTORCODE 
		FROM dbo.QueueAttendee q
		JOIN dbo.InterrogationReports irp ON irp.CaseId=q.CaseId
		WHERE q.Id=@QId)
	IF(@DCode IS NULL OR LEN(@DCode)=0)
		CONTINUE;
	SET @DId=(SELECT TOP 1 bu.UserId 
		FROM dbo.Base_User bu 
		WHERE bu.EnCode=@DCode
		);
	--此处可能产生重复账号
	IF(@DId IS NULL)
	BEGIN	    
		SET @DId=NEWID();
		INSERT INTO dbo.Base_User
		        (UserId,EnCode,Account,RealName,NickName,
		         DeleteMark,EnabledMark,English_Name
		         )
		SELECT @DId,@DCode,@DCode,@DCode,@DCode,
			1,0,@DCode
	END

	INSERT dbo.AppointmentMain
	        (Id,ClinicId,Type,PatientMainId,ServiceId,Date,StartTime,Interval,
	         Status,CreateTime,CreatePerson,
	         IsNoShow,IsDelete,SubServiceId,ServiceItem,AppointmentWay,DoctorId,
	         EnCode)
	SELECT @AId,ClinicId,(
		SELECT TOP 1 ItemDetailId FROM dbo.Base_DataItemDetail
		WHERE ItemValue='Consultation'
	),q.PatientMainId,@DId,q.Date,'10:00',15,2,q.CreateTime,q.CreatePerson,
	0,0,
	(
		SELECT TOP 1 d1.ItemDetailId FROM dbo.Base_DataItemDetail d
		JOIN Base_DataItemDetail d1 ON d1.ParentId=d.ItemDetailId
		WHERE d.ItemValue='Consultation'
	),'',3,@DId,q.CaseId
	FROM dbo.QueueAttendee q
	WHERE Id=@QId;
END
CLOSE _CUR
DEALLOCATE _CUR


IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('MedicinePreparation') AND max_length<=100 AND name='MaterialName')
	ALTER TABLE MedicinePreparation
	ALTER COLUMN MaterialName NVARCHAR(200) NULL
GO
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('MedicinePreparation') AND name='Z__CaseId')
	ALTER TABLE MedicinePreparation
	ADd Z__CaseId NVARCHAR(200) NULL
GO

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('ActionPrescriptionRecord') AND name='Z__CLINICCODE')
	ALTER TABLE ActionPrescriptionRecord
	ADd Z__CLINICCODE NVARCHAR(200) NULL
GO

--根据问诊开药记录补全取药信息
IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('ActionPrescriptionRecord') AND name='Z__CLINICCODE')
BEGIN
    INSERT INTO dbo.MedicinePreparation
        (Id,QueueAttendeeId,EnCode,MaterialId,MaterialName,PackingUnitId,
         PackingNumber,CurrencyUnit,UnitPrice,TotalPrice,UseId,DaysOfTaking,
         TimesPerDay,EachDose,EachDoseUnit,AppointmentTotal,TakingRemark,
         DeliveryAmoun,IsCheck,Remark,CreateDate,CreateUserId,CreateUserName,
         Duration)
	SELECT NEWID() AS Id, ISNULL((
		SELECT TOP 1 Id FROM dbo.QueueAttendee q
		WHERE q.CaseId=x.CaseId
	),'NUL') AS QueueAttendeeId,x.CaseID,x.MaterialId,x.MaterialName,x.PackingUnitId
	,x.DemandNum,x.Currency,x.UnitPrice,x.TotalPrice,x.UseId,x.DaysOfTaking
	,x.TimesPerDay,x.EachDose,x.EachDoseUnit,x.TotalQty,x.TakingRemark
	,x.DemandNum,0,'补全取药信息',GETDATE(),x.CreateUserId,x.CreateUserName,1
	FROM ActionPrescriptionRecord x
	WHERE LEN(x.Z__CLINICCODE)>0
	AND  NOT EXISTS(
		SELECT 1 FROM dbo.MedicinePreparation x1
		WHERE x1.Z__CaseId=x.CaseID
	)
END
GO



--根据队列信息补充预约信息
DECLARE @QId NVARCHAR(200),@CaseId NVARCHAR(200),@AId NVARCHAR(200);
DECLARE _CUR CURSOR STATIC READ_ONLY FOR
SELECT q.CaseId,q.Id 
FROM dbo.QueueAttendee q
WHERE AppointmentId IS NULL AND LEN(CASEId)>0
OPEN _CUR 
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @CaseId,@QId;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @AId=NEWID();
	INSERT INTO dbo.AppointmentMain
        (Id,ClinicId,Type,PatientMainId,ServiceId,Date,StartTime,Interval,
         Status,CreateTime,CreatePerson,Memo,
         IsNoShow,IsDelete,DoctorId,_PK)
	SELECT @AId,qa.ClinicId,'',qa.PatientMainId,'',qa.Date,dbo.F_GetFormatDate(qa.CreateTime,'HH:mm'),15,
	3,qa.CreateTime,qa.CreatePerson,qa.MEMO,
	0,0,'恢复数据完整性',qa.CaseId
	FROM dbo.QueueAttendee qa
	WHERE qa.Id=@QId

	UPDATE dbo.QueueAttendee
	SET AppointmentId=@AId
	WHERE Id=@QId
END
CLOSE _CUR
DEALLOCATE _CUR
GO


UPDATE dbo.AppointmentMain
SET Date=LEFT(Date,4)+'-'+RIGHT( LEFT(Date,6),2)+'-'+RIGHT(Date,2)
WHERE LEN(Date)=8


UPDATE dbo.QueueAttendee
SET Date=LEFT(Date,4)+'-'+RIGHT( LEFT(Date,6),2)+'-'+RIGHT(Date,2)
WHERE LEN(Date)=8
GO

UPDATE dbo.QueueAttendee
SET QueueTime=dbo.F_GetFormatDate(QueueTime,'HH:mm') 
WHERE LEN(QueueTime)>5


--补充数据309 可能产生冗余的问诊记录
DECLARE _CUR CURSOR READ_ONLY STATIC FOR
SELECT CaseId FROM dbo.InterrogationReports
--WHERE Enabled=1 
GROUP BY CaseId
HAVING COUNT(*)>1

DECLARE @CaseId NVARCHAR(200),@Id NVARCHAR(200)
OPEN _CUR
WHILE(1=1)
BEGIN
    FETCH NEXT FROM _CUR INTO @CaseId;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @Id=(SELECT TOP 1 Id FROM dbo.InterrogationReports 
	WHERE Enabled=1 AND CaseId=@CaseId)

	IF(@Id IS NULL)
		SET @Id=(SELECT TOP 1 Id FROM dbo.InterrogationReports where CaseId=@CaseId)

	DELETE InterrogationReports
	WHERE CaseId=@CaseId AND Id!=@Id
	
	PRINT @CaseId
	PRINT @Id
END
CLOSE _CUR;
DEALLOCATE _CUR;


UPDATE dbo.AppointmentMain
SET Date=LEFT(Date,4)+'-'+RIGHT( LEFT(Date,6),2)+'-'+RIGHT(Date,2)
WHERE LEN(Date)=8


UPDATE dbo.QueueAttendee
SET Date=LEFT(Date,4)+'-'+RIGHT( LEFT(Date,6),2)+'-'+RIGHT(Date,2)
WHERE LEN(Date)=8
GO

UPDATE dbo.QueueAttendee
SET QueueTime=dbo.F_GetFormatDate(QueueTime,'HH:mm') 
WHERE LEN(QueueTime)>5

UPDATE dbo.AppointmentMain
SET StartTime=dbo.F_GetFormatDate(StartTime,'HH:mm') 
WHERE LEN(StartTime)>5


--清理重复的备药记录
DELETE dbo.MedicinePreparation
WHERE Id
IN(
	SELECT mp.Id
	FROM dbo.MedicinePreparation mp
	WHERE EXISTS(
		SELECT 1 FROM dbo.MedicinePreparation mp1
		WHERE mp1.QueueAttendeeId=mp.QueueAttendeeId
		AND mp1.MaterialId=mp.MaterialId
		AND mp1.Id>mp.Id
	)
)
GO


UPDATE dbo.ActionPrescriptionRecord
SET EachDoseUnit=(
	SELECT TOP 1 bd.ItemDetailId FROM dbo.Base_DataItemDetail bd
	JOIN dbo.Base_DataItem bi ON bi.ItemId=bd.ItemId
	WHERE bi.ItemCode='PackingUnit' AND (bd.ItemValue=Z__UNIT OR bd.ItemName=Z__UNIT)
)
,TotalQty=Z__QTY
WHERE LEN(Z__QTY)>0 AND (EachDoseUnit IS NULL OR EachDoseUnit LIKE 'NUL%' )

UPDATE dbo.ActionPrescriptionRecord
SET PackingUnitId=EachDoseUnit
WHERE LEN(Z__QTY)>0 AND (PackingUnitId IS NULL OR PackingUnitId LIKE 'NUL%' ) AND EachDoseUnit IS NOT NULL

UPDATE dbo.MedicinePreparation
SET PackingUnitId=(
	SELECT TOP 1 ap.EachDoseUnit FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
)
,EachDoseUnit=(
	SELECT TOP 1 ap.EachDoseUnit FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
)
WHERE EXISTS(
	SELECT 1 FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
	AND LEN(ap.Z__CLINICCODE)>0
) AND (PackingUnitId IS NULL OR PackingUnitId LIKE 'NUL%' )


UPDATE dbo.MedicinePreparation
SET AppointmentTotal=(
	SELECT SUM(ap.TotalQty) FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
)
,PackingNumber=(
	SELECT SUM(ap.TotalQty) FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
)
WHERE EXISTS(
	SELECT 1 FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
	AND LEN(ap.Z__CLINICCODE)>0
) 
GO


UPDATE dbo.MedicinePreparation
SET 
 TimesPerDayName=(
	SELECT TOP 1 ap.Z__FREQUENCY
	FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
),
EachDoseName=(
	SELECT TOP 1 ap.Z__DOSAGE
	FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
)
WHERE EXISTS(
	SELECT 1 FROM dbo.ActionPrescriptionRecord ap
	WHERE ap.CaseID=dbo.MedicinePreparation.EnCode
	AND ap.MaterialId=dbo.MedicinePreparation.MaterialId 
	AND LEN(ap.Z__CLINICCODE)>0
)

UPDATE dbo.ActionPrescriptionRecord
SET TimesPerDayName=Z__FREQUENCY
,EachDoseName=Z__DOSAGE
WHERE LEN(Z__CLINICCODE)>0



IF EXISTS(SELECT 1 FROM Sys.columns WHERE name='Z__TIMESTAMP' AND object_id=OBJECT_ID('LaboratoryQueue'))
BEGIN
	UPDATE  dbo.LaboratoryQueue 
	set Status=3
	WHERE Status<>3 AND 
	LEN(Z__TIMESTAMP)>0
END
GO

UPDATE dbo.LaboratoryQueue
SET Status=0,Enabled=0
WHERE LEN(Z__CLINICCODE)>0
GO

--更新文件名，让文件名带后缀名
IF EXISTS(SELECT * FROM sys.columns WHERE name='Z__FILENAME' AND object_id=OBJECT_ID('PatientMainFile'))
BEGIN
	UPDATE dbo.PatientMainFile
	SET FileName=FileName+'.'+FileCategory
	WHERE FileName NOT LIKE '%.%'
	AND LEN(Z__FILENAME)>0
END
GO
			  
--对账单、账单明细的金额部分，除100	  
IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('BillInfo') AND name='Z__CLINICCODE')
AND NOT EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('BillInfo') AND name='Z__CLINICCODE')
BEGIN
    ALTER TABLE BillInfo
	ADD _INIT BIT NULL
	ALTER TABLE BillFeeDetail
	ADD _INIT BIT NULL
END
GO
IF EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('BillInfo') AND name='Z__CLINICCODE')
AND EXISTS(SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('BillInfo') AND name='_INIT')
BEGIN
	   UPDATE dbo.BillInfo
	   SET AccountReceivable=AccountReceivable/100
	   ,PaidAmount=PaidAmount/100
	   ,UnpaidAmount=UnpaidAmount/100
	   ,_INIT=1
	   WHERE LEN(_PK)>0
	   AND _INIT IS NULL


	   UPDATE BillFeeDetail 
	   SET Price=Price/100
	   ,UnitPrice=UnitPrice/100
	   ,_INIT=1
	   WHERE LEN(_PK)>0
	   AND _INIT IS NULL
END
GO


UPDATE dbo.BillFeeDetail
SET BillId=(
	SELECT TOP 1 bi.Id FROM dbo.BillInfo bi
	WHERE bi.BillNo=dbo.BillFeeDetail.BillNo
	AND bi.Z__CLINICCODE=dbo.BillFeeDetail.Z__CLINICCODE
)
--,PayServiceType=(
--	SELECT TOP 1 d.ItemDetailId
--	FROM dbo.Base_DataItemDetail d
--	JOIN dbo.Base_DataItem i ON i.ItemId=d.ItemId AND i.ItemCode='Service Type'
--	WHERE d.ItemValue=dbo.BillFeeDetail.Z__ITEM
--)
,CaseId=(
	SELECT TOP 1 bi.CaseId FROM dbo.BillInfo bi
	WHERE bi.BillNo=dbo.BillFeeDetail.BillNo
	AND bi.Z__CLINICCODE=dbo.BillFeeDetail.Z__CLINICCODE
)
WHERE LEN(_PK)>0 
AND (BillId IS NULL OR caseId IS NULL)


--合并问诊记录
DECLARE _CUR CURSOR READ_ONLY STATIC FOR
SELECT Id,Z__P_CODE,Z__DOCTORCODE,Z__DATE,Z__CLINICCODE
FROM dbo.InterrogationReports
WHERE LEN(_PK)>0
DECLARE @Id NVARCHAR(200),@P_CODE NVARCHAR(200),@DOCTORCODE NVARCHAR(200),@DATE  NVARCHAR(200),@CLINICCODE  NVARCHAR(200)
,@Findings NVARCHAR(MAX),@Complaint NVARCHAR(MAX),@Diagnosis NVARCHAR(MAX)
,@TF NVARCHAR(MAX),@TC  NVARCHAR(MAX),@TD NVARCHAR(MAX)
,@i INT 

SET @i=1
OPEN _CUR
WHILE(1=1)
BEGIN
	SET @i=@i-1;
    FETCH NEXT FROM _CUR INTO @Id,@P_CODE,@DOCTORCODE,@DATE,@CLINICCODE;
	IF(@@FETCH_STATUS!=0)BREAK;
	SET @Findings='';
	SET @Complaint='';
	SET @Diagnosis='';
	SET @P_CODE=@P_CODE COLLATE Chinese_PRC_CI_AS
	SET @CLINICCODE=@CLINICCODE COLLATE Chinese_PRC_CI_AS
	SET @DOCTORCODE=@DOCTORCODE COLLATE Chinese_PRC_CI_AS
	SET @DATE=@DATE COLLATE Chinese_PRC_CI_AS
	
	DECLARE _CUR1 CURSOR STATIC READ_ONLY FOR
	SELECT COMPLAINT,FINDING,DIAGNOSIS FROM [数据源].dbo.MEDICALRECORDTABLE
	WHERE P_CODE=@P_CODE
	AND CLINICCODE=@CLINICCODE
	AND DOCTORCODE=@DOCTORCODE
	AND DATE=@DATE
	ORDER BY TIMESTAMP ASC

	OPEN _CUR1
	WHILE(1=1)
	BEGIN
	    FETCH NEXT FROM _CUR1 INTO @TC,@TF,@TD;
		IF(@@FETCH_STATUS!=0)BREAK;
		IF(LEN(@TC)>0)
		BEGIN
			IF(@Complaint='')
				SET @Complaint=@TC COLLATE Chinese_PRC_CI_AS
			ELSE
				SET @Complaint=@Complaint+'\r\n'+@TC COLLATE Chinese_PRC_CI_AS
		END
		IF(LEN(@TF)>0)
		BEGIN
			IF(@Findings='')
				SET @Findings=@TF COLLATE Chinese_PRC_CI_AS
			ELSE
				SET @Findings=@Findings+'\r\n'+@TF COLLATE Chinese_PRC_CI_AS
		END
		IF(LEN(@TD)>0)
		BEGIN
			IF(@Diagnosis='')
				SET @Diagnosis=@TD COLLATE Chinese_PRC_CI_AS
			ELSE
				SET @Diagnosis=@Diagnosis+'\r\n'+@TD COLLATE Chinese_PRC_CI_AS
		END
	END
	CLOSE _CUR1
	DEALLOCATE _CUR1

	UPDATE dbo.InterrogationReports
	SET Complaint=@Complaint
	,Findings=@Findings
	,Diagnosis=@Diagnosis
	WHERE Id=@Id

	PRINT @Complaint;
	PRINT @Findings
	PRINT @Diagnosis
	PRINT @Id
END
CLOSE _CUR
DEALLOCATE _CUR



GO
